package net.zhengxinyun.performance.service.exportService;

import com.sirdc.modules.utils.ObjectUtils;
import com.sirdc.modules.utils.StringUtils;
import net.zhengxinyun.performance.Util.DateUtils;
import net.zhengxinyun.performance.Util.Util;
import net.zhengxinyun.performance.dto.ExcelDTO;
import net.zhengxinyun.performance.entity.*;
import net.zhengxinyun.performance.filter.*;
import net.zhengxinyun.performance.mapper.Department.DepartmentEntityMapper;
import net.zhengxinyun.performance.mapper.Indicators.CountryIndicatorsEntityMapper;
import net.zhengxinyun.performance.mapper.Indicators.TownIndicatorsEntityMapper;
import net.zhengxinyun.performance.mapper.Indicators.TownScoreEntityMapper;
import net.zhengxinyun.performance.mapper.incentive.IncentiveEntityMapper;
import net.zhengxinyun.performance.mapper.incentive.IncentiveType;
import net.zhengxinyun.performance.service.exam.exam.ExamService;
import net.zhengxinyun.performance.service.incentive.IncentiveService;
import net.zhengxinyun.performance.service.login.UserService;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;
import org.apache.poi.xssf.usermodel.*;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.beans.factory.annotation.Value;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.web.multipart.MultipartFile;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.*;
import java.util.concurrent.CopyOnWriteArrayList;
import java.util.concurrent.locks.ReadWriteLock;
import java.util.stream.Collectors;

@Service
@Transactional
public class ExportExccelServiceImpl implements ExportService {

    @Value("${word-file}")
    private String filePath;

    @Autowired
    private TownIndicatorsEntityMapper indicatorMapper;

    @Autowired
    private TownScoreEntityMapper scoreMapper;

    @Autowired
    private DepartmentEntityMapper departmaper;

    @Autowired
    private UserService userService;

    @Autowired
    private CountryIndicatorsEntityMapper cityIndexMapper;

    @Autowired
    private ExamService  examService;

    @Autowired
    private ExcelDtoService excelDtoService;
    @Autowired
    private IncentiveEntityMapper incentiveMapper;

    @Autowired
    private IncentiveType typeAndItemMapper;

    /**
     * 导出表
     *
     * @param datetime
     * @return
     */
    @Override
    public HSSFWorkbook getAllIndiction(String datetime) {
        String[] head = {"塘前乡", "葛岭镇", "樟城镇", "城峰镇", "清凉镇",
                "富泉乡", "岭路乡", "赤锡乡", "梧桐镇", "嵩口镇", "洑口乡",
                "盖洋乡", "长庆镇", "东洋乡", "霞拔乡", "同安镇", "大洋镇",
                "盘谷乡", "红星乡", "白云乡", "丹云乡"};

        List<DepartmentFilter> departlist = departmaper.query(new DepartmentFilter());
        HashMap<String, String> nameToCode = new HashMap<>();
        HashMap<String, String> codeToName = new HashMap<>();
        for (DepartmentFilter item : departlist) {
            nameToCode.put(item.getDepartAddr(), item.getDepartcode());
            codeToName.put(item.getDepartcode(), item.getDepartAddr());
        }

        TownIndicatorsFilter filter = new TownIndicatorsFilter();
        filter.setIndexType("二级指标");
        filter.setAvail(1);
        String year = datetime.substring(0, 4);
        System.out.println("=======================" + year + "=========================");
        // filter.setDateTime(year);
        filter.setDateTime(year);
        //所有二级指标
        List<TownIndicatorsFilter> indictorlist = indicatorMapper.query(filter);
        filter.setIndexType(null);
        filter.setDateTime(datetime);


        HSSFWorkbook wb = new HSSFWorkbook();//创建一个对应Excel文件
        HSSFSheet sheet = wb.createSheet("完成情况表"); //床建excel 中的sheet
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        HSSFCellStyle style2 = wb.createCellStyle();
        style2.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        HSSFRow row = sheet.createRow(0);
        sheet.autoSizeColumn(1, true);
        //设置合并单元格
        //设置表头
        HSSFCell cell0 = row.createCell(0);
        cell0.setCellValue("指标名称");
        sheet.setColumnWidth(0, 20 * 512);
        HSSFCell cell1 = row.createCell(1);
        // cell1.setCellValue("权重/得分率/排名"  ) ;
        cell1.setCellStyle(style);
        sheet.setColumnWidth(1, 8 * 512);

        //表头
        for (int i = 0; i < head.length; i++) {
            HSSFCell cell = row.createCell(i + 2);
            cell.setCellValue(head[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i + 2, 5 * 512);
        }
        //该指标下所有乡镇的权重， 得分率 排名。
        int colum = 0;
        int rows = 0;
        for (TownIndicatorsFilter item : indictorlist) {
            System.out.println("------------------" + item.getIndexName() + "------------------------------");
            rows++;
            row = sheet.createRow(rows);
            Cell cellName = row.createCell(0);
            CellRangeAddress region1 = new CellRangeAddress(rows, rows + 2, 0, 0);
            cellName.setCellValue(item.getIndexName());
            sheet.addMergedRegion(region1);
            cellName.setCellStyle(style2);
            filter.setIndexId(item.getId() + "");
            List<TownIndicatorsFilter> scoreLists = scoreMapper.getExportIndexScore(filter);
            //row.createCell(0).setCellValue();
            //表头
            for (int i = 0; i < head.length; i++) {
                //设置权重
                Cell cellcol1 = row.createCell(1);
                cellcol1.setCellStyle(style);
                cellcol1.setCellValue("权重");
                Cell cell = row.createCell(i + 2);
                cell.setCellStyle(style);
                for (TownIndicatorsFilter score : scoreLists) {
                    if (head[i].equals(codeToName.get(score.getDepartCode()))) {
                        cell.setCellValue(score.getWeight());
                        //cell.setCellStyle(style);
                    }
                }
            }
            rows = rows + 1;
            row = sheet.createRow(rows);
            Cell cellcol2 = row.createCell(1);
            cellcol2.setCellStyle(style);
            cellcol2.setCellValue("得分率");
            // row.createCell(1).setCellValue("得分率");
            for (int i = 0; i < head.length; i++) {
                Cell cell = row.createCell(i + 2);
                cell.setCellStyle(style);
                //设置得分率
                for (TownIndicatorsFilter score : scoreLists) {
                    if (head[i].equals(codeToName.get(score.getDepartCode()))) {
                        // Cell cell =row.createCell(i+2);
                        cell.setCellValue(score.getScore());
                        // cell.setCellStyle(style);
                    }
                }
            }
            rows = rows + 1;
            row = sheet.createRow(rows);
            Cell cellcol3 = row.createCell(1);
            cellcol3.setCellStyle(style);
            cellcol3.setCellValue("排名");
            //row.createCell(1).setCellValue("排名");
            for (int i = 0; i < head.length; i++) {
                Cell cell = row.createCell(i + 2);
                cell.setCellStyle(style);
                //设置排名
                for (TownIndicatorsFilter score : scoreLists) {
                    if (head[i].equals(codeToName.get(score.getDepartCode()))) {
                        // Cell cell =row.createCell(i+2);
                        cell.setCellValue(score.getRank());
                        // cell.setCellStyle(style);
                        // row.createCell(i+2).setCellValue(score.getRank());
                    }
                }
            }
            //----
        }
        //获得总列数
        int rowNum = sheet.getLastRowNum();
        int num = (rowNum) / 3;
        System.out.println(rowNum);
        //合并单元格
       /* for (int i = 1; i <=num; i++) {
            CellRangeAddress region1 = new CellRangeAddress(i*3-2, i*3, 0,0);
            sheet.addMergedRegion(region1);

        }*/
        /* cell0.setCellStyle(style);*/
        return wb;
    }

    // 指标分析 word文档
    @Override
    public Map<String, Object> getIndictionWordInfo() {
        return null;
    }

    /**
     * 县直单位模板下载
     *
     * @return
     */
    @Override
    public HSSFWorkbook getCountyDataMould() {

        String[] head1 = {"department", "score"};
        String[] head2 = {"被考核单位", "分数"};

        DepartmentFilter filter = new DepartmentFilter();
        filter.setDepartType("县直单位");
        List<DepartmentFilter> departmentList = departmaper.query(filter);

        HSSFWorkbook wb = new HSSFWorkbook();//创建一个对应Excel文件
        HSSFSheet sheet = wb.createSheet("县直单位指标考核数据模板"); //创建excel中的sheet
        sheet.autoSizeColumn(1, true);

        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

        //第一行
        HSSFRow row1 = sheet.createRow(0);
        //第二行
        HSSFRow row2 = sheet.createRow(1);
        //设置表头
        //表头1
        for (int i = 0; i < head1.length; i++) {
            HSSFCell cell = row1.createCell(i);
            cell.setCellValue(head1[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 5 * 2500);
        }

        //表头2
        for (int i = 0; i < head2.length; i++) {
            HSSFCell cell = row2.createCell(i);
            cell.setCellValue(head2[i]);
            cell.setCellStyle(style);
            sheet.setColumnWidth(i, 5 * 2500);
        }
        int rows = 2;
        for (DepartmentFilter d : departmentList) {
            HSSFRow row = sheet.createRow(rows);
            for (int i = 0; i < 2; i++) {
                HSSFCell cell = row.createCell(i);
                if(i == 0){
                    cell.setCellValue(d.getDepartment());
                }
                cell.setCellStyle(style);
                sheet.setColumnWidth(i, 5 * 2500);
            }
            rows++;
        }
        return wb;
    }

    /**
     * 市指标数据
     * @return
     */
    @Override
    public XSSFWorkbook getCityIndexDate(String datetime,String orderBy) {
    String path  = filePath+"cityDataExcel.xlsx";

        System.out.println("----------"+path+"-------------");

        //查询数据月份数据
        CountryIndicatorsFilter filter = new CountryIndicatorsFilter();
        filter.setDateTime(datetime);
        filter.setOrderBy(orderBy);
        List<CountryIndicatorsFilter> list = cityIndexMapper.queryByTwoTable(filter);
        List<CountryIndicatorsFilter> listdata=null;
        List<CountryIndicatorsFilter> listnull=null;

        if("rank".equals(orderBy)){

            listdata =list.stream().sorted( Comparator.comparingInt(CountryIndicatorsFilter::getRank)  ).filter(countryIndicatorsFilter -> countryIndicatorsFilter.getRank()!=0). collect(Collectors.toList());
            listnull=list.stream().filter(countryIndicatorsFilter -> countryIndicatorsFilter.getRank()==0).collect(Collectors.toList());
            listdata.addAll(listnull);
            list = listdata;
        }else if("sort".equals(orderBy)) {
            list=list.stream().sorted(Comparator.comparingInt(CountryIndicatorsFilter::getSort)).collect(Collectors.toList());

        } else  if("finalScore".equals(orderBy)){
            listdata = list.stream().filter(p -> p.getFinalScore()!=null).sorted(Comparator.comparingDouble(CountryIndicatorsFilter::getFinalScore).reversed()  ).collect(Collectors.toList());
            listnull= list.stream().filter(p -> p.getFinalScore()==null).collect(Collectors.toList());
            listdata.addAll(listnull);
            list=listdata;


        }

        File file  = new File(path);

        XSSFWorkbook wb  = null;

        try {
            wb=new XSSFWorkbook(new FileInputStream(file));
            XSSFCellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中格式
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

            XSSFSheet sheet = wb.getSheetAt(0);
            int rows = 2;
            int i =0;
            for(CountryIndicatorsFilter item:list)
            {
               i++;
                XSSFRow row  = sheet.getRow(rows);
                if(row==null)
                {
                    row = sheet.createRow(rows);
                }
                XSSFCell cell = row.createCell(0);
                cell.setCellValue(i);
                cell.setCellStyle(style);

                XSSFCell cell1 = row.createCell(1);
                cell1.setCellValue(item.getLeadUnitName());
                cell1.setCellStyle(style);

                XSSFCell cell2 = row.createCell(2);
                cell2.setCellValue((item.getSort()==0?"":item.getSort()+"、" ) + item.getIndexName());
                cell2.setCellStyle(style);

                String rank = item.getRank()==0?"-":item.getRank()+"";
                XSSFCell cell3 = row.createCell(3);
                cell3.setCellValue(rank);
                cell3.setCellStyle(style);


                String finalScore =item.getFinalScore()==null?"":item.getFinalScore().toString();
                XSSFCell cell4 = row.createCell(4);
                cell4.setCellValue(finalScore);
                cell4.setCellStyle(style);

                rows++;
            }




        } catch (IOException e) {
            e.printStackTrace();
        }finally {

        }


        return wb;
    }

    @Override
    public HSSFWorkbook exportUsers(String deparcode) {

        UserFilter userFilter = new UserFilter();
        userFilter.setDepartcode(deparcode);
        List<UserFilter> list = userService.queryUsers(userFilter);
        CopyOnWriteArrayList<UserFilter> copylist  = new CopyOnWriteArrayList(list);
        for (UserFilter user:copylist)
        {
                if("admin".equals(user.getUserPermissions()))
                {
                    copylist.remove(user);
                }
        }

        HSSFWorkbook wb = new HSSFWorkbook();//创建一个对应Excel文件
        HSSFSheet sheet = wb.createSheet("完成情况表"); //床建excel 中的sheet
        HSSFCellStyle style = wb.createCellStyle();
        style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中格式
        style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
        HSSFRow row = sheet.createRow(0);
        sheet.autoSizeColumn(1, true);
        row.createCell(0).setCellValue("用户ID");
        sheet.setColumnWidth(0, 4 * 512);
        row.createCell(1).setCellValue("用户名");
        sheet.setColumnWidth(1, 10 * 512);
        row.createCell(2).setCellValue("昵称");
        sheet.setColumnWidth(2, 10 * 512);
        row.createCell(3).setCellValue("密码");
        sheet.setColumnWidth(3, 16 * 512);
        row.createCell(4).setCellValue("用户单位");
        sheet.setColumnWidth(4, 15 * 512);
        row.createCell(5).setCellValue("单位编码");
        sheet.setColumnWidth(5, 5 * 512);
        row.createCell(6).setCellValue("角色");
        sheet.setColumnWidth(6, 8 * 512);
        row.createCell(7).setCellValue("角色ID");
        sheet.setColumnWidth(7, 5 * 512);
        row.createCell(8).setCellValue("是否管理员");
        sheet.setColumnWidth(8, 5 * 512);
        row.createCell(9).setCellValue("职务");
        sheet.setColumnWidth(9, 8 * 512);
        row.createCell(10).setCellValue("联系电话");
        sheet.setColumnWidth(10, 8 * 512);
        int rows = 0;
        for(UserFilter item : copylist)
        {
            rows++;
            row = sheet.createRow(rows);
            row.createCell(0).setCellValue(item.getId());
            row.createCell(1).setCellValue(item.getUserName());
            row.createCell(2).setCellValue(item.getNickName());
            row.createCell(3).setCellValue(item.getUserPassword());
            row.createCell(4).setCellValue(item.getUserDepartment() );
            row.createCell(5).setCellValue(item.getDepartcode() );
            row.createCell(6).setCellValue(item.getUserPermissions() );
            String roleid = ObjectUtils.isBlank(item.getRoleid())?null:item.getRoleid().toString();
            row.createCell(7).setCellValue( roleid  );
            row.createCell(8).setCellValue(item.getIsManger());
            row.createCell(9).setCellValue(item.getUserPosition());
            row.createCell(10).setCellValue(item.getUserPhone());

        }

        return  wb;
    }

    /**
     * 市对县指标分析 导出 word
     * @param datetime
     * @param topNum
     * @return
     */
    @Override
    public HashMap<String, Object> getCityIndexReport(String datetime,int topNum) {

        CountryIndicatorsFilter filter = new CountryIndicatorsFilter();
        filter.setDateTime(datetime.substring(0,4));
        filter.setMonthTime(datetime);
        List<CountryIndicatorsFilter> list = cityIndexMapper.queryByTwoTable(filter);
        List<HashMap<String,Object>> goodlist = new ArrayList<>();
        List<HashMap<String,Object>> badList = new ArrayList<>();
        List<HashMap<String,Object>> noReportList= new ArrayList<>();
        for (CountryIndicatorsFilter item : list) {

            if(item.getRank()<=topNum&&item.getRank()>0){
                HashMap<String,Object> goodmap = new HashMap<>();

                goodmap.put("indexName",item.getIndexName());
                goodmap.put("depart",item.getLeadUnitName());
                goodmap.put("indexRank",item.getRank());
                goodlist.add(goodmap);
            }
            if(item.getRank()>topNum)
            {
                HashMap<String,Object> badmap = new HashMap<>();
                badmap.put("indexRank",item.getRank());
                badmap.put("indexName",item.getIndexName());
                badmap.put("depart",item.getLeadUnitName());

                badList.add(badmap);
            }
           if(null==item.getScore()&&item.getRank()==0){
               HashMap<String,Object> noReport = new HashMap<>();

               noReport.put("indexName",item.getIndexName());
               noReport.put("depart",item.getLeadUnitName());

               noReportList.add(noReport);
           }



        }
        HashMap<String,Object> map = new HashMap<>();



        if(goodlist.size()==0)
        {
            HashMap<String,Object> goodmap = new HashMap<>();
            goodmap.put("indexName","无");
            goodmap.put("indexRank","-");
            goodmap.put("depart","-");
            goodlist.add(goodmap);
            map.put("goodSize",0);
        }else {
            map.put("goodSize",goodlist.size());
        }
        map.put("goodList",goodlist);

        if(badList.size()==0)
        {
            HashMap<String,Object> badmap = new HashMap<>();
            badmap.put("depart","-");
            badmap.put("indexName","无");
            badmap.put("indexRank","-");
            badList.add(badmap);
            map.put("badSize",0);
        }else {
            map.put("badSize",badList.size());
        }
        map.put("badList",badList);

        if(noReportList.size() ==0)
        {
            HashMap<String,Object> report = new HashMap<>();
            report.put("indexName","无");
            report.put("depart","无");
            noReportList.add(report);


            map.put("reportSize",0);
        }else {
            map.put("reportSize",noReportList.size());
        }
        map.put("reportList",noReportList);







        return map;
    }


    @Override
    public int uploadExcel(MultipartFile file,Long examId) {

        System.out.println("开始处理文件");

        try {
            InputStream inp  = file.getInputStream();
            Workbook wb;
            wb = new XSSFWorkbook(inp);

            Sheet sheet = wb.getSheetAt(0);//获取第一个表格

            if(sheet==null)
            {
                return  1; //表格数据为空
            }


            int code = matchExcelHead(sheet);
            if(code !=0)
            {
                return  code ;//表头错误
            }

            //读取数据

            List<ExamQuestionEntity> stemList  = new ArrayList<>();

            for(int i =1 ;i<=sheet.getLastRowNum();i++)
            {
                ExamQuestionEntity questionEntity = new ExamQuestionEntity();

                Row row = sheet.getRow(i);
                Cell cell0 = row.getCell(0);
                if(ObjectUtils.isBlank(cell0))
                {
                    cell0=row.createCell(0);
                }
                cell0.setCellType(Cell.CELL_TYPE_STRING);
                String sort = cell0.getStringCellValue();

                questionEntity.setSort(Integer.parseInt(sort)); //设置序号


                Cell cell1 = row.getCell(1);
                if(ObjectUtils.isBlank(cell1))
                {
                    cell1= row.createCell(1);
                }
                cell1.setCellType(Cell.CELL_TYPE_STRING);
                String stem = cell1.getStringCellValue();
                questionEntity.setStem(stem);//问卷题干


                Cell cell2 = row.getCell(2);
                if(ObjectUtils.isBlank(cell2))
                {
                    cell2= row.createCell(2);
                }
                cell2.setCellType(Cell.CELL_TYPE_STRING);
                String op1 = cell2.getStringCellValue();
                questionEntity.setSelectA(op1);// 选项1


                Cell cell3 = row.getCell(3);
                if(ObjectUtils.isBlank(cell3))
                {
                    cell3= row.createCell(3);
                }
                cell3.setCellType(Cell.CELL_TYPE_STRING);
                String op2 = cell3.getStringCellValue();
                questionEntity.setSelectB(op2);// 选项2


                Cell cell4 = row.getCell(4);
                if(ObjectUtils.isBlank(cell4))
                {
                    cell4= row.createCell(4);
                }
                cell4.setCellType(Cell.CELL_TYPE_STRING);
                String op3 =  cell4.getStringCellValue();
                questionEntity.setSelectC(op3);

                Cell cell5 = row.getCell(5);
                if(ObjectUtils.isBlank(cell5))
                {
                    cell5= row.createCell(5);
                }
                cell5.setCellType(Cell.CELL_TYPE_STRING);
                String op4 =  cell5.getStringCellValue();
                questionEntity.setSelectD(op4);

                Cell cell6 = row.getCell(6);
                if(ObjectUtils.isBlank(cell6))
                {
                    cell6= row.createCell(6);
                }
                cell6.setCellType(Cell.CELL_TYPE_STRING);
                String op5 =  cell6.getStringCellValue();
                questionEntity.setSelectE(op5);

                Cell cell7 = row.getCell(7);
                if(ObjectUtils.isBlank(cell7))
                {
                    cell7= row.createCell(7);
                }
                cell7.setCellType(Cell.CELL_TYPE_STRING);
                String op6 =  cell7.getStringCellValue();
                questionEntity.setSelectF(op6);

                Cell cell8 = row.getCell(8);
                if(ObjectUtils.isBlank(cell8))
                {
                    cell8= row.createCell(8);
                }
                cell8.setCellType(Cell.CELL_TYPE_STRING);
                String w1 =  cell8.getStringCellValue();

                w1= StringUtils.isBlank(w1)?"0":w1;
                questionEntity.setWeightA(Integer.parseInt(w1));

                Cell cell9 = row.getCell(9);
                if(ObjectUtils.isBlank(cell9))
                {
                    cell9= row.createCell(9);
                }
                cell9.setCellType(Cell.CELL_TYPE_STRING);
                String w2 =  cell9.getStringCellValue();
                w2= StringUtils.isBlank(w2)?"0":w2;
                questionEntity.setWeightB(Integer.parseInt(w2));

                Cell cell10 = row.getCell(10);
                if(ObjectUtils.isBlank(cell10))
                {
                    cell10= row.createCell(10);
                }
                cell10.setCellType(Cell.CELL_TYPE_STRING);
                String w3 =  row.getCell(10).getStringCellValue();

                w3= StringUtils.isBlank(w3)?"0":w3;
                questionEntity.setWeightC(Integer.parseInt(w3));


                Cell cell11 = row.getCell(11);
                if(ObjectUtils.isBlank(cell11))
                {
                    cell11= row.createCell(11);
                }
                cell11.setCellType(Cell.CELL_TYPE_STRING);
                String w4 =  cell11.getStringCellValue();

                w4= StringUtils.isBlank(w4)?"0":w4;
                questionEntity.setWeightD(Integer.parseInt(w4));


                Cell cell12 = row.getCell(12);
                if(ObjectUtils.isBlank(cell12))
                {
                    cell12= row.createCell(12);
                }
                cell12.setCellType(Cell.CELL_TYPE_STRING);
                String w5 =  cell12.getStringCellValue();

                w5= StringUtils.isBlank(w5)?"0":w5;
                questionEntity.setWeightE(Integer.parseInt(w5));


                Cell cell13 = row.getCell(13);
                if(ObjectUtils.isBlank(cell13))
                {
                    cell13= row.createCell(13);
                }
                cell13.setCellType(Cell.CELL_TYPE_STRING);
                String w6 =  cell13.getStringCellValue();

                w6= StringUtils.isBlank(w6)?"0":w6;
                questionEntity.setWeightF(Integer.parseInt(w6));


               questionEntity.setExamId(examId);
                stemList.add(questionEntity);

            }

            int i = examService.saveQuestionlist(stemList);
            if(i==0)
            {
                return  3;//保存失败
            }


        } catch (IOException e) {
            e.printStackTrace();
        }
        return  0;

    }

    /**]
     * 匹配表头
     * @param sheet
     * @return
     */
    int matchExcelHead(Sheet sheet)
    {
        Row tableHeader = sheet.getRow(0);//

        tableHeader.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
        String sort =  tableHeader.getCell(0).getStringCellValue();
        if(!"序号".equals(sort))
        {
            return  2;//表头错误
        }

        tableHeader.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
        String stem =  tableHeader.getCell(1).getStringCellValue();
        if(!"问卷题干".equals(stem))
        {
            return  2;//表头错误
        }

        tableHeader.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
        String op1 =  tableHeader.getCell(2).getStringCellValue();
        if(!"选项1".equals(op1))
        {
            return  2;//表头错误
        }

        tableHeader.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
        String op2 =  tableHeader.getCell(3).getStringCellValue();
        if(!"选项2".equals(op2))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
        String op3 =  tableHeader.getCell(4).getStringCellValue();
        if(!"选项3".equals(op3))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
        String op4 =  tableHeader.getCell(5).getStringCellValue();
        if(!"选项4".equals(op4))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
        String op5 =  tableHeader.getCell(6).getStringCellValue();
        if(!"选项5".equals(op5))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
        String op6 =  tableHeader.getCell(7).getStringCellValue();
        if(!"选项6".equals(op6))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
        String w1 =  tableHeader.getCell(8).getStringCellValue();
        if(!"选项1权值".equals(w1))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
        String w2 =  tableHeader.getCell(9).getStringCellValue();
        if(!"选项2权值".equals(w2))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
        String w3 =  tableHeader.getCell(10).getStringCellValue();
        if(!"选项3权值".equals(w3))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
        String w4 =  tableHeader.getCell(11).getStringCellValue();
        if(!"选项4权值".equals(w4))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
        String w5 =  tableHeader.getCell(12).getStringCellValue();
        if(!"选项5权值".equals(w5))
        {
            return  2;//表头错误
        }
        tableHeader.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
        String w6 =  tableHeader.getCell(13).getStringCellValue();
        if(!"选项6权值".equals(w6))
        {
            return  2;//表头错误
        }



        return  0;

    }


    /**
     * 县指标管理上传模板。
     * @param file
     * @param
     */
    @Override
    public HashMap<String,Object> uploadTownIndex(MultipartFile file) {
        HashMap<String,Object> map = new HashMap<>();

        String batchNum = DateUtils.getCurrentDateTimeNum();
        InputStream inp  = null;
        try {
            inp = file.getInputStream();
            Workbook wb;
           // wb = new XSSFWorkbook(inp);
            wb = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);

            if(sheet==null){

                System.out.println("表格数据为空");
                map.put("code",500);
                map.put("message","表格数据为空");
                return map;
            }
            //校验表头
            String indexlHead = matchTownIndexlHead(sheet);
            if(!"ok".equals(indexlHead))
            {
                map.put("code",500);
                map.put("message",indexlHead);
                return  map;
            }

            List<TownIndicatorsEntity> oneIndexList = new ArrayList<>();
            List<TownIndicatorsEntity> twoIndexList = new ArrayList<>();
            List<TownEntity>         weightList = new ArrayList<>();

            List<ExcelDTO> dtolists = new ArrayList<>();

            int lastRow = sheet.getPhysicalNumberOfRows();

            for (int i = 1; i < lastRow; i++) {
                Row row = sheet.getRow(i);

                ExcelDTO dto = new ExcelDTO();
                dto.setAvail(1);
                String remark = "";
                String head="";
                //一级指标
                Cell cell0 = row.getCell(0);
                if(cell0==null){
                    cell0 = row.createCell(0);
                }
                cell0.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell0.getStringCellValue();
                dto.setCell0(head);


                //二级指标名称
                row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(1).getStringCellValue();
                dto.setCell1(head);

                //塘前乡
                row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(2).getStringCellValue();

                dto.setCell2(head);


                //葛岭镇
                row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(3).getStringCellValue();
                dto.setCell3(head);
                //樟城镇
                row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(4).getStringCellValue();
                dto.setCell4(head);

                //城峰镇
                row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(5).getStringCellValue();
                dto.setCell5(head);

                //清凉镇
                row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(6).getStringCellValue();
                dto.setCell6(head);

                //富泉乡
                row.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(7).getStringCellValue();
                dto.setCell7(head);

                //岭路乡
                row.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(8).getStringCellValue();
                dto.setCell8(head);

                //赤锡乡
                row.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(9).getStringCellValue();
                dto.setCell9(head);

                //梧桐镇
                row.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(10).getStringCellValue();
                dto.setCell10(head);


                //嵩口镇
                row.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(11).getStringCellValue();
                dto.setCell11(head);

                //洑口乡
                row.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(12).getStringCellValue();
                dto.setCell12(head);

                //盖洋乡
                row.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(13).getStringCellValue();
                dto.setCell13(head);

                //长庆镇
                row.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(14).getStringCellValue();
                dto.setCell14(head);

                //东洋
                row.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(15).getStringCellValue();
                dto.setCell15(head);

                //霞拔乡
                row.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(16).getStringCellValue();
                dto.setCell16(head);

                //同安镇
                row.getCell(17).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(17).getStringCellValue();
                dto.setCell17(head);

                //大洋镇
                row.getCell(18).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(18).getStringCellValue();
                dto.setCell18(head);

                //盘古乡
                row.getCell(19).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(19).getStringCellValue();
                dto.setCell19(head);

                //红星
                row.getCell(20).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(20).getStringCellValue();
                dto.setCell20(head);

                //白云
                row.getCell(21).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(21).getStringCellValue();
                dto.setCell21(head);

                //丹云
                row.getCell(22).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(22).getStringCellValue();
                dto.setCell22(head);

                //牵头单位
                row.getCell(23).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(23).getStringCellValue();
                List<DepartmentEntity> name =null;
                dto.setName23(head);
                if(StringUtils.isNotBlank(head))
                {
                    name = departmaper.getDepartByNameList(head);
                    if(ObjectUtils.isBlank(name))
                    {
                        remark ="未根据关键字【"+head+"】匹配到牵头单位;";
                        dto.setAvail(0);
                        dto.setCell23(null);
                    }else
                    {
                        if(name.size()>1)
                        {
                            remark ="匹配到多个相同的牵头单位，单位可能存在重复，请联系管理员;";
                            dto.setAvail(0);
                            dto.setCell23(null);
                        }else {

                            dto.setCell23(name.get(0).getDepartcode());
                            //dto.setName23(name.get(0).getDepartment());
                            remark = "根据关键字【" + head + "】匹配到牵头单位【" + name.get(0).getDepartment() + "】";
                        }
                    }
                }
                else
                {
                    remark ="未匹配到牵头单位,关键字字段为空;";
                    dto.setAvail(0);
                    dto.setCell23(null);
                }
                //责任单位
                row.getCell(24).setCellType(Cell.CELL_TYPE_STRING);
                head =  row.getCell(24).getStringCellValue();
                dto.setCell24(head);
                dto.setName24(head);
           /*     if(StringUtils.isNotBlank(head))
                {
                    name = departmaper.getDepartByNameList(head);
                    if(ObjectUtils.isBlank(name))
                    {
                        remark =remark+"未根据关键字【"+head+"】匹配到责任单位;";
                        dto.setCell24(null);
                        dto.setAvail(0);

                    }else
                    {
                        if(name.size()>1){
                            remark =remark+"匹配到多个责任单位，单位可能存在重复，请联系管理员;";
                            dto.setCell24(null);
                            dto.setAvail(0);
                        }else {
                            dto.setCell24(name.get(0).getDepartcode());
                            dto.setName24(name.get(0).getDepartment());
                        }


                    }
                }
                else
                {
                    remark =remark+"未匹配到责任单位,关键字为空;";
                    dto.setAvail(0);
                    dto.setCell24(null);
                }*/

                dto.setRemark(remark);
                //生成批次号
                dto.setBatchNum(batchNum);
                dtolists.add(dto);
            }

            int i = excelDtoService.saveData(dtolists);
            if(i==0)
            {
                map.put("code",500);
                map.put("message","导入失败");
                return map;
            }


        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }finally {
            try {
                inp.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        map.put("code",200);
        map.put("message","上传校验成功");
        map.put("batchNum",batchNum);

        return  map;
    }



    /**]
     * 匹配县对乡镇表头
     * @param sheet
     * @return
     */
    String  matchTownIndexlHead(Sheet sheet)
    {
        Row tableHeader = sheet.getRow(0);//

        String head="";
        tableHeader.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(0).getStringCellValue();
        if(!"一级指标".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《一级指标》";//表头错误
        }

        tableHeader.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(1).getStringCellValue();
        if(!"二级指标".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《二级指标》";//表头错误
        }

        tableHeader.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(2).getStringCellValue();
        if(!"塘前乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《塘前乡》";//表头错误
        }

        tableHeader.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(3).getStringCellValue();
        if(!"葛岭镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《葛岭镇》";//表头错误
        }
        tableHeader.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
         head =  tableHeader.getCell(4).getStringCellValue();
        if(!"樟城镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《樟城镇》";//表头错误
        }
        tableHeader.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(5).getStringCellValue();
        if(!"城峰镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《城峰镇》";//表头错误
        }

        tableHeader.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(6).getStringCellValue();
        if(!"清凉镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《清凉镇》";//表头错误
        }

        tableHeader.getCell(7).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(7).getStringCellValue();
        if(!"富泉乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《富泉乡》";//表头错误
        }


        tableHeader.getCell(8).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(8).getStringCellValue();
        if(!"岭路乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《岭路乡》";//表头错误
        }
        tableHeader.getCell(9).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(9).getStringCellValue();
        if(!"赤锡乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《赤锡乡》";//表头错误
        }
        tableHeader.getCell(10).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(10).getStringCellValue();
        if(!"梧桐镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《梧桐镇》";//表头错误
        }
        tableHeader.getCell(11).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(11).getStringCellValue();
        if(!"嵩口镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《嵩口镇》";//表头错误
        }
        tableHeader.getCell(12).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(12).getStringCellValue();
        if(!"洑口乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《洑口乡》";//表头错误
        }
        tableHeader.getCell(13).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(13).getStringCellValue();
        if(!"盖洋乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《盖洋乡》";//表头错误
        }


        tableHeader.getCell(14).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(14).getStringCellValue();
        if(!"长庆镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《长庆镇》";//表头错误
        }



        tableHeader.getCell(15).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(15).getStringCellValue();
        if(!"东洋乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《东洋乡》";//表头错误
        }


        tableHeader.getCell(16).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(16).getStringCellValue();
        if(!"霞拔乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《霞拔乡》";//表头错误
        }


        tableHeader.getCell(17).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(17).getStringCellValue();
        if(!"同安镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《同安镇》";//表头错误
        }


        tableHeader.getCell(18).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(18).getStringCellValue();
        if(!"大洋镇".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《大洋镇》";//表头错误
        }


        tableHeader.getCell(19).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(19).getStringCellValue();
        if(!"盘谷乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《盘谷乡》";//表头错误
        }

        tableHeader.getCell(20).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(20).getStringCellValue();
        if(!"红星乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《红星乡》";//表头错误
        }

        tableHeader.getCell(21).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(21).getStringCellValue();
        if(!"白云乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《白云乡》";//表头错误
        }

        tableHeader.getCell(22).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(22).getStringCellValue();
        if(!"丹云乡".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《丹云乡》";//表头错误
        }

        tableHeader.getCell(23).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(23).getStringCellValue();
        if(!"牵头单位".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《牵头单位》";//表头错误
        }

        tableHeader.getCell(24).setCellType(Cell.CELL_TYPE_STRING);
        head =  tableHeader.getCell(24).getStringCellValue();
        if(!"责任单位".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《责任单位》";//表头错误
        }


        return  "ok";

    }


    /**
     * 市对县指标管理上传
     *
     */
    @Override
    public HashMap<String, Object> uploadCityIndex(MultipartFile file) {

        HashMap<String,Object> map  = new HashMap<>();

        String batchNum  = DateUtils.getCurrentDateTimeNum();
        InputStream ips = null;
        Workbook wb = null;
        try {
            ips = file.getInputStream();

         /*   wb = WorkbookFactory.create(ips);*/

            String fileName = file.getOriginalFilename();
            if(fileName.endsWith("xls"))
            {
                wb = new  HSSFWorkbook(ips);
            }else if (fileName.endsWith("xlsx"))
            {
                wb = new XSSFWorkbook(ips);
            }

            Sheet sheet = wb.getSheetAt(0);
            if(sheet==null)
            {
                //System.out.println("表格数据为空");
                map.put("code",500);
                map.put("message","表格数据为空");
                return map;
            }
            //表头
            String cityHead = matchCityHead(sheet);
            if(!"ok".equals(cityHead))
            {
                map.put("code",500);
                map.put("message",cityHead);
                return map;
            }


            List<ExcelDTO> excelDTOs = new ArrayList<>();
            List<CountryIndicatorsEntity> twoIndex = new ArrayList<>();
            int lastRow =  sheet.getPhysicalNumberOfRows();
            for (int i = 1; i <lastRow; i++) {
                Row row  = sheet.getRow(i);
                ExcelDTO dto = new ExcelDTO();
                String head="",remark="";

                dto.setAvail(1);
                //二级指标名称
                Cell cell0 = row.getCell(1);
                if(cell0==null){
                    cell0 = row.createCell(1);
                }
                cell0.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell0.getStringCellValue();
                dto.setCell0(head);

                //权重
                Cell cell1 = row.getCell(4);
                if(cell1==null){
                    cell1 = row.createCell(4);
                }
                cell1.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell1.getStringCellValue();
                dto.setCell1(head);

              /*  //牵头单位
                Cell cell2 = row.getCell(2);
                if(cell2==null){
                    cell2 = row.createCell(2);
                }
                cell2.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell2.getStringCellValue();
                dto.setCell2(head);*/

                // 牵头单位
                Cell cell23 = row.getCell(5);
                if(cell23==null){
                    cell23 = row.createCell(5);
                }
                cell23.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell23.getStringCellValue();
                dto.setCell23(head);
                dto.setName23(head);
               List< DepartmentEntity> departmentEntity =null;
                if(StringUtils.isNotBlank(head))
                {

                    departmentEntity = departmaper.getDepartByNameList(head.trim());
                    if(ObjectUtils.isBlank(departmentEntity))
                    {
                        remark ="未根据关键字【"+head+"】匹配到牵头单位";
                        dto.setCell23(null);
                        dto.setAvail(0);
                    }else {
                        if(departmentEntity.size()>1){

                            remark= "根据关键字匹配到多个或者重复的牵头单位:";
                            String depart="";
                            for (DepartmentEntity entity : departmentEntity) {
                                depart =entity.getDepartment()+","+depart;
                            }
                            remark=remark+depart;
                            dto.setCell23(null);
                            dto.setAvail(0);
                        }else {
                            dto.setCell23(departmentEntity.get(0).getDepartcode());
                            //dto.setName23(departmentEntity.get(0).getDepartment());
                            remark= "根据关键字【"+head +"】匹配到【"+departmentEntity.get(0).getDepartment()+"】";
                        }

                    }

                }else {
                    remark ="未根据关键字【"+head+"】匹配到牵头单位";
                    dto.setCell23(null);
                    dto.setAvail(0);
                }


                //责任单位
                Cell cell24 = row.getCell(6);
                if(cell24==null){
                    cell24 = row.createCell(6);
                }
                cell24.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell24.getStringCellValue();
                dto.setCell24(head);
                dto.setName24(head);
            /*    if(StringUtils.isNotBlank(head))
                {

                    departmentEntity = departmaper.getDepartByNameList(head.trim());
                    if(ObjectUtils.isBlank(departmentEntity))
                    {
                        remark =remark+","+"未根据关键字【"+head+"】匹配到责任单位";
                        dto.setCell24(null);
                        dto.setAvail(0);
                    }else {

                        if(departmentEntity.size()>1){

                            remark= remark+","+"根据关键字匹配到多个或者重复的责任单位:";
                            String depart="";
                            for (DepartmentEntity entity : departmentEntity) {
                                depart =entity.getDepartment()+","+depart;
                            }
                            dto.setAvail(0);
                            remark=remark+depart;
                            dto.setCell24(null);

                        }else {
                            dto.setCell24(departmentEntity.get(0).getDepartcode());
                            dto.setName24(departmentEntity.get(0).getDepartment());
                        }
                    }

                }else {
                    remark =remark+","+"责任单位为空,未匹配到责任单位";
                    dto.setCell24(null);
                    dto.setAvail(0);
                }*/

                //一级指标
                Cell cell4 = row.getCell(0);
                if(cell4==null){
                    cell4 = row.createCell(0);
                }
                cell4.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell4.getStringCellValue();
                dto.setCell4(head);


                // 标准值
                Cell cell5 = row.getCell(2);
                if(cell5==null){
                    cell5 = row.createCell(2);
                }
                cell5.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell5.getStringCellValue();
                dto.setCell5(head);


                // 方向
                Cell cell6 = row.getCell(3);
                if(cell6==null){
                    cell6 = row.createCell(3);
                }
                cell6.setCellType(Cell.CELL_TYPE_STRING);
                head =  cell6.getStringCellValue();
                dto.setCell6(head);


                dto.setBatchNum(batchNum);
                dto.setRemark(remark);
                excelDTOs.add(dto);
            }
            int i = excelDtoService.saveData(excelDTOs);
            if(i==0)
            {
                map.put("code",500);
                map.put("message","导入失败");
                return map;
            }


        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            try {
                ips.close();


            } catch (IOException e) {
                e.printStackTrace();
            }
        }

        map.put("code",200);
        map.put("message","上传校验成功");
        map.put("batchNum",batchNum);

        return  map;
    }

    /**
     * 校验实现县表头
     * @param sheet
     * @return
     */
    String matchCityHead(Sheet sheet)
    {

        Row row  = sheet.getRow(0);
        String head ="";
        row.getCell(1).setCellType(Cell.CELL_TYPE_STRING);
        head =  row.getCell(1).getStringCellValue();
        if(!"二级指标".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《二级指标》";//表头错误
        }

        row.getCell(4).setCellType(Cell.CELL_TYPE_STRING);
        head =  row.getCell(4).getStringCellValue();
        if(!"权重".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《权重》";//表头错误
        }

        row.getCell(5).setCellType(Cell.CELL_TYPE_STRING);
        head =  row.getCell(5).getStringCellValue();
        if(!"牵头单位".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《牵头单位》";//表头错误
        }

        row.getCell(6).setCellType(Cell.CELL_TYPE_STRING);
        head =  row.getCell(6).getStringCellValue();
        if(!"责任单位".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《责任单位》";//表头错误
        }
        row.getCell(0).setCellType(Cell.CELL_TYPE_STRING);
        head =  row.getCell(0).getStringCellValue();
        if(!"一级指标".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《一级指标》";//表头错误
        }

        row.getCell(2).setCellType(Cell.CELL_TYPE_STRING);
        head =  row.getCell(2).getStringCellValue();
        if(!"标准值".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《标准值》";//表头错误
        }

        row.getCell(3).setCellType(Cell.CELL_TYPE_STRING);
        head =  row.getCell(3).getStringCellValue();
        if(!"方向".equals(head))
        {
            return "表头:《"+head+"》有误，应为：《方向》";//表头错误
        }

        return  "ok";

    }


    /**
     * 导出正向激励文件
     * @param datetime
     * @return
     */
    @Override
    public Workbook exportIncentive(String datetime) {

        String path = filePath + "incentiveExcel.xls";
        JiliTypeExce otherfj = new JiliTypeExce();
        IncentiveFilter filter = new IncentiveFilter();
        filter.setAudit("1");
        filter.setDateTime(datetime);
        List<IncentiveFilter> query = incentiveMapper.query(filter);

        File  file = new File(path);
        Workbook wb = null;

        try {
            wb =WorkbookFactory.create(new FileInputStream(file));
            CellStyle style = wb.createCellStyle();
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//居中格式
            style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);
            style.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
            style.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
            style.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
            style.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框

            Sheet sheet = wb.getSheetAt(0);
            //设置日期
            Row rowdate = sheet.getRow(2);
            if(rowdate==null)
            {
                rowdate= sheet.createRow(2);
            }
            Cell celldate = rowdate.getCell(2);
            //cell0.setCellStyle(style);
            String reportDate = DateUtils.getCurrentDate();
            celldate.setCellValue("填报日期："+reportDate.substring(0,4)+"年"+reportDate.substring(4,6)+"月"+reportDate.substring(6,8)+"日"); // 序号

            int rows = 4;
            int i =0;
            double sumScore=0.0;
            for (IncentiveFilter item : query) {
                i++;
                Row row = sheet.getRow(rows);
                if(row==null)
                {
                    row= sheet.createRow(rows);
                }
                Cell cell0 = row.createCell(0);
                cell0.setCellStyle(style);
                cell0.setCellValue(i); // 序号

                Cell cell1 = row.createCell(1);
                cell1.setCellValue(item.getIncentive()); // 激励事项
                cell1.setCellStyle(style);

                Cell cell2 = row.createCell(2);
                cell2.setCellValue(item.getPoint()); // 分值
                cell2.setCellStyle(style);
                sumScore=sumScore+Double.parseDouble(item.getPoint());


                Cell cell3 = row.createCell(3);
                cell3.setCellValue(""); // 备注
                cell3.setCellStyle(style);

                rows++;
            }
            if (query.size()>0)
            {
                Row  row  = sheet.getRow(rows);
                if(row==null)
                {
                    row= sheet.createRow(rows);
                }
                CellRangeAddress region = new CellRangeAddress(rows,rows,0,1);
                sheet.addMergedRegion(region);
                RegionUtil.setBorderBottom(1,region,sheet,wb);
                RegionUtil.setBorderLeft(1,region,sheet,wb);
                RegionUtil.setBorderRight(1,region,sheet,wb);
                RegionUtil.setBorderTop(1,region,sheet,wb);

                Cell heji = row.createCell(0);

                heji.setCellValue("合计"); // 备注
                heji.setCellStyle(style);



                Cell hejiSum = row.createCell(2);
                region = new CellRangeAddress(rows,rows,2,3);
                RegionUtil.setBorderBottom(1,region,sheet,wb);
                RegionUtil.setBorderLeft(1,region,sheet,wb);
                RegionUtil.setBorderRight(1,region,sheet,wb);
                RegionUtil.setBorderTop(1,region,sheet,wb);
                sheet.addMergedRegion(region);
                hejiSum.setCellValue(sumScore); // 备注
                hejiSum.setCellStyle(style);
            }


            //  附件2
            List<IncentiveFilter> biaozhangList = query.stream().filter(incentiveFilter -> incentiveFilter.getTypeName().contains("受到表彰")).collect(Collectors.toList());
            otherfj.OtherFujianItem(wb,biaozhangList,style,1);

            // 附件3
            List<IncentiveFilter> tuiguangList = query.stream().filter(incentiveFilter -> incentiveFilter.getTypeName().contains("推广经验")).collect(Collectors.toList());
            otherfj.OtherFujianItem(wb,tuiguangList,style,2);

            // 附件4
            List<IncentiveFilter> pishiKenDingList = query.stream().filter(incentiveFilter -> incentiveFilter.getTypeName().contains("批示肯定")).collect(Collectors.toList());
            otherfj.OtherFujianItem(wb,pishiKenDingList,style,3);

            // 附件5
            List<IncentiveFilter> dianXingFaYanList = query.stream().filter(incentiveFilter -> incentiveFilter.getTypeName().contains("典型发言")).collect(Collectors.toList());
            otherfj.OtherFujianItem(wb,dianXingFaYanList,style,4);

            // 附件6
            List<IncentiveFilter> topRankList = query.stream().filter(incentiveFilter -> incentiveFilter.getTypeName().contains("排名靠前")).collect(Collectors.toList());
            otherfj.OtherFujianItem(wb,topRankList,style,5);



        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }


        return wb;
    }


    @Override
    public HashMap<String, Object> uploadIncentiveType(MultipartFile filte) {
        HashMap<String,Object> map = new HashMap<>();
        InputStream inp  =null;
        String errorMsg="";
        try {
            inp = filte.getInputStream();
            Workbook wb  = WorkbookFactory.create(inp);
            Sheet sheet = wb.getSheetAt(0);// 创建工作铺
            if(sheet==null)
            {

                map.put("code",500);
                map.put("message","表格数据为空");
                System.out.println("表格数据为空");
                return map;
            }


            List<IncentiveTypeEntity> listType = new ArrayList<>();
            int  lastrow =  sheet.getPhysicalNumberOfRows();
            for (int i = 1; i < lastrow; i++) {
                Row row = sheet.getRow(i);
                IncentiveTypeEntity  types = new IncentiveTypeEntity();

                Cell cell0 = row.getCell(0);
                if(cell0==null){
                    cell0 = row.createCell(0);
                    errorMsg="日期为必填，不能为空";
                    map.put("code",500);
                    map.put("message",errorMsg);
                    return  map;
                }
                cell0.setCellType(Cell.CELL_TYPE_STRING);
                String datetime =  cell0.getStringCellValue();
                //.setCell0(datetime);
                types.setDatetime(datetime);

               /* Cell cell1 = row.getCell(1);
                if(cell1==null){
                    cell1 = row.createCell(1);
                    errorMsg="分类为必填，不能为空";
                    map.put("code",500);
                    map.put("message",errorMsg);
                    return  map;
                }
                cell1.setCellType(Cell.CELL_TYPE_STRING);
                String clas =  cell1.getStringCellValue();
                types.setTypeClass(clas);*/


                Cell cell2 = row.getCell(1);
                if(cell2==null){
                    cell2 = row.createCell(1);
                    errorMsg="类别名称为必填，不能为空";
                    map.put("code",500);
                    map.put("message",errorMsg);
                    return  map;
                }
                cell2.setCellType(Cell.CELL_TYPE_STRING);
                String typeName =  cell2.getStringCellValue();
                types.setTypeName(typeName.trim());

                List<IncentiveTypeItemEntity> listItem = new ArrayList<>();

                int cellNum = row.getPhysicalNumberOfCells();
                if(cellNum>=2){
                    for (int i1 = 2; i1 < cellNum; i1++) {
                        Cell cell = row.getCell(i1);
                        cell.setCellType(Cell.CELL_TYPE_STRING);
                        String itemName =  cell.getStringCellValue();
                        IncentiveTypeItemEntity item = new IncentiveTypeItemEntity();
                        item.setItemName(itemName);
                        listItem.add(item);
                    }
                }

                types.setListItem(listItem);
                listType.add(types);
            }
            //保存前删除旧数据

            //typeAndItemMapper.deleteTypeByDatetime(da)
            CopyOnWriteArrayList<IncentiveTypeEntity> copylist = new CopyOnWriteArrayList(listType);
            for (IncentiveTypeEntity entity : copylist) {
                IncentiveTypeEntity type = new IncentiveTypeEntity();
                type.setDatetime(entity.getDatetime());
                type.setTypeName(entity.getTypeName().trim());


                List<IncentiveTypeFilter> incentiveTypeFilters = typeAndItemMapper.queryType(type);
                if(incentiveTypeFilters.size()>0)
                {
                    /*for (IncentiveTypeFilter filter : incentiveTypeFilters) {
                        typeAndItemMapper.deleteType(filter.getId());
                    }*/
                    copylist.remove(entity);
                }


            }

            //保存加分类别，大类
            int i = typeAndItemMapper.batchSaveType(copylist);
            if(i>0){
                for (IncentiveTypeEntity type : copylist) {
                    List<IncentiveTypeItemEntity> listItem = type.getListItem();
                    listItem.forEach(entity -> entity.setTypeId(type.getId()));
                    typeAndItemMapper.insertItems(listItem);
                }
            }


            //map.put("list",listType);


        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        map.put("code",200);

        return map;
    }
}
